﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.UI;
using Telerik.Web.UI;

public partial class Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            EnsureChartStoredProcedures();
        }
    }

    private static void EnsureChartStoredProcedures()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
        var script = @"
            IF OBJECT_ID(N'dbo.ChartOrdersByEmployee', N'P') IS NULL
                EXEC(N'CREATE PROCEDURE dbo.ChartOrdersByEmployee AS BEGIN SET NOCOUNT ON; SELECT 1 AS Placeholder; END');

            EXEC(N'
                ALTER PROCEDURE dbo.ChartOrdersByEmployee
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT CONCAT(e.FirstName, '' '', e.LastName) AS EmployeeName,
                           COUNT(*) AS TotalOrders
                    FROM Orders o
                    INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
                    GROUP BY e.FirstName, e.LastName
                    ORDER BY TotalOrders DESC;
                END
            ');

            IF OBJECT_ID(N''dbo.ChartSalesByCategory'', N''P'') IS NULL
                EXEC(N''CREATE PROCEDURE dbo.ChartSalesByCategory AS BEGIN SET NOCOUNT ON; SELECT 1 AS Placeholder; END'');

            EXEC(N''
                ALTER PROCEDURE dbo.ChartSalesByCategory
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT c.CategoryName,
                           CAST(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS decimal(18, 2)) AS TotalSales
                    FROM [Order Details] od
                    INNER JOIN Products p ON p.ProductID = od.ProductID
                    INNER JOIN Categories c ON c.CategoryID = p.CategoryID
                    GROUP BY c.CategoryName
                    ORDER BY TotalSales DESC;
                END
            '');

            IF OBJECT_ID(N''dbo.ChartMonthlyOrders'', N''P'') IS NULL
                EXEC(N''CREATE PROCEDURE dbo.ChartMonthlyOrders AS BEGIN SET NOCOUNT ON; SELECT 1 AS Placeholder; END'');

            EXEC(N''
                ALTER PROCEDURE dbo.ChartMonthlyOrders
                AS
                BEGIN
                    SET NOCOUNT ON;

                    SELECT CONVERT(varchar(7), OrderDate, 120) AS OrderMonth,
                           COUNT(*) AS TotalOrders
                    FROM Orders
                    WHERE OrderDate IS NOT NULL
                    GROUP BY CONVERT(varchar(7), OrderDate, 120)
                    ORDER BY OrderMonth;
                END
            '');";

        using (var connection = new SqlConnection(connectionString))
        using (var command = new SqlCommand(script, connection))
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}
